ALTER TABLE dbo.THUE_PHONG
DROP CONSTRAINT FK__THUE_PHON__SO_TH__25869641

ALTER TABLE dbo.THUE_PHONG
DROP CONSTRAINT FK__THUE_PHON__MA_KH__24927208


ALTER TABLE dbo.THUE_PHONG
DROP COLUMN SO_THUE_PHONG

ALTER TABLE dbo.THUE_PHONG
DROP COLUMN MA_KHACH_THUE


ALTER TABLE KHACH_THUE
ADD 
SO_THUE_PHONG INT FOREIGN KEY REFERENCES THUE_PHONG(ID), 
LEVEL INT  



ALTER TABLE dbo.THUE_PHONG
DROP COLUMN TRANG_THAI



CREATE PROC SP_INSERT_THUE_PHONG
@MA_PHONG INT, 
@NGAY_THUE DATE, 
@TIEN_COC FLOAT, 
@USER_ID INT,
@retval NVARCHAR(10) OUTPUT
AS
BEGIN
	DECLARE @ID_RETURN INT
	INSERT INTO 
	dbo.THUE_PHONG
	(
		MA_PHONG,
		NGAY_THUE,
		TIEN_COC,
		CREATE_BY,
		UPDATE_BY,
		CREATE_AT,
		UPDATE_AT,
		ACTIVE
	)
	VALUES
	(
		@MA_PHONG,
		@NGAY_THUE,
		@TIEN_COC,
		@USER_ID,
		@USER_ID,
		GETDATE(), 
		GETDATE(),
		'A'		
	);
	SET @ID_RETURN = (SELECT SCOPE_IDENTITY());
	SET @retval = CONVERT(Nvarchar(10), @ID_RETURN);
END

exec SP_GET_KHACH_THUE_BY_STP '1'

alter PROC SP_GET_KHACH_THUE_BY_STP
@SO_THUE_PHONG INT
AS
BEGIN
	SELECT 
		KHACH_THUE.ID,
		KHACH_THUE.HO_VA_TEN,
		KHACH_THUE.HINH_ANH,
		KHACH_THUE.NGAY_SINH,
		KHACH_THUE.GIOI_TINH,
		KHACH_THUE.CMND,
		KHACH_THUE.NGAY_CAP,
		KHACH_THUE.NOI_CAP,
		KHACH_THUE.DIA_CHI ,
		KHACH_THUE.SO_DT ,
		KHACH_THUE.SO_THUE_PHONG ,
		KHACH_THUE.LEVEL
	FROM
	KHACH_THUE
	WHERE 
	ACTIVE = 'A'
	AND SO_THUE_PHONG = @SO_THUE_PHONG
	ORDER BY LEVEL
END


ALTER PROC [dbo].[SP_GET_THUE_PHONG_LIST]
@SEARCH_STRING NVARCHAR(MAX)
AS
BEGIN
	SELECT 
		THUE_PHONG.ID, 
		THUE_PHONG.MA_PHONG, 
		KHACH_THUE.HO_VA_TEN,
		KHACH_THUE.HINH_ANH,
		PHONG.SO_PHONG,
		ISNULL(THU_TIEN.NGAY_THU, GETDATE()) NGAY_THU, 
		ISNULL(THU_TIEN.NGAY_KET_THUC_KY_NAY,GETDATE()) NGAY_KET_THUC_KY_NAY
	FROM 
		THUE_PHONG
		JOIN PHONG ON THUE_PHONG.MA_PHONG = PHONG.ID
		LEFT JOIN THU_TIEN ON THUE_PHONG.ID = THU_TIEN.SO_THUE_PHONG
		JOIN KHACH_THUE ON THUE_PHONG.ID = KHACH_THUE.SO_THUE_PHONG		
	WHERE
		THUE_PHONG.ACTIVE = 'A'
		AND KHACH_THUE.LEVEL = 1
		AND (@SEARCH_STRING = '' OR @SEARCH_STRING IS NULL OR UPPER(KHACH_THUE.HO_VA_TEN) LIKE '%' + UPPER(@SEARCH_STRING) + '%' OR  CAST(PHONG.SO_PHONG AS NVARCHAR) = @SEARCH_STRING )
		AND KHACH_THUE.ACTIVE = 'A'
	ORDER BY THU_TIEN.NGAY_KET_THUC_KY_NAY
END


CREATE PROC SP_INSERT_KHACH_THUE
@HO_VA_TEN NVARCHAR(200), 
@NGAY_SINH DATE, 
@GIOI_TINH CHAR(1), 
@CMND VARCHAR(20), 
@NGAY_CAP DATE,
@NOI_CAP NVARCHAR(100), 
@DIA_CHI NVARCHAR(200), 
@SO_DT VARCHAR(20), 
@SO_THUE_PHONG INT,
@HINH_ANH VARCHAR(MAX), 
@LEVEL INT, 
@USR_ID INT
AS
BEGIN
	DECLARE @ID_RETURN INT
	INSERT INTO dbo.KHACH_THUE
	(
		HO_VA_TEN, 
		NGAY_SINH, 
		GIOI_TINH, 
		CMND, 
		NGAY_CAP, 
		NOI_CAP, 
		DIA_CHI, 
		CREATE_BY, 
		UPDATE_BY, 
		CREATE_AT, 
		UPDATE_AT,		
		ACTIVE, 
		SO_DT,
		SO_THUE_PHONG, 
		LEVEL
	)
	VALUES
	(
		@HO_VA_TEN,
		@NGAY_SINH, 
		@GIOI_TINH, 
		@CMND,
		@NGAY_CAP, 
		@NOI_CAP,
		@DIA_CHI, 
		@USR_ID, 
		@USR_ID, 
		GETDATE(), 
		GETDATE(),
		'A', 
		@SO_DT,
		@SO_THUE_PHONG,
		@LEVEL
	)
	SET @ID_RETURN = (SELECT SCOPE_IDENTITY())
	UPDATE KHACH_THUE
	SET HINH_ANH = (@HINH_ANH + @ID_RETURN + '.jpg')
	WHERE
	ID = @ID_RETURN
END


